<?php
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of AthleteModel
 *
 * @author Marko
 */
class Athlete_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->model('Team_model', 'TeamModel');
    }

    /**
     * Fetches all athletes from database
     * @param type $withTeams
     * @param type $limit
     * @param type $offset
     * @return array result array
     */
    public function fetchAll($withTeams = false, $limit = MAX_INT, $offset = 0) {

        $this->db->select('`id`,`first_name`,`last_name`,`dob`,`gender`,`team_id`');
        $this->db->from('athletes');
        $this->db->limit($limit, $offset);
        $athletes = $this->db->get()->result('Athlete');
        if ($withTeams) {

            foreach ($athletes as &$athlete) {
                $query = $this->db->get_where('teams', array('id' => $athlete->getTeamID()));
                $athlete->setTeam($query->row(0, 'Team'));
            }
        }

        $data['athletes'] = $athletes;
        $data['count'] = $this->db->count_all_results('athletes');
        return $data;
    }

    public function fetchAllLikeJson() {
        $this->db->select('`id` as athlete_no,`first_name`,`last_name`,`dob`,`gender`,`team_id`');
        $this->db->from('athletes');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function fetchAllWithResults() {

        $this->db->select('`athletes.id`,`first_name`,`last_name`,`dob`,`gender`,`team_id`, count(`results`.`id`) as results');
        $this->db->from('athletes');
        $this->db->join('results', 'results.athlete_id = athletes.id', 'left');
        $this->db->group_by('athletes.id');
        $athletes = $this->db->get()->result('Athlete');
        return $athletes;
    }

    /**
     * Searches database for queried names.
     * @param type $query_array
     * @param type $withTeams
     * @param type $limit
     * @param type $offset
     * @return array results with count and athletes
     */
    public function search($query_array, $withTeams, $limit = MAX_INT, $offset = 0) {

        $this->db->select('`id`,`first_name`,`last_name`,`dob`,`gender`,`team_id`')
                ->from('athletes')
                ->limit($limit, $offset);

        if (strlen($query_array['fname']) || strlen($query_array['lname'])) {
            if (strlen($query_array['fname']) == 0) {
                $this->db->like('last_name', $query_array['lname']);
            } else if (strlen($query_array['lname']) == 0) {
                $this->db->like('first_name', $query_array['fname']);
            } else {
                $this->db->like('first_name', $query_array['fname']);
                $this->db->like('last_name', $query_array['lname']);
            }
        }

        $query = $this->db->get();
        $athletes = $query->result('Athlete');
        $data['athletes'] = $athletes;

        if ($withTeams) {

            foreach ($athletes as &$athlete) {
                $query = $this->db->get_where('teams', array('id' => $athlete->getTeamID()));
                $athlete->setTeam($query->row(0, 'Team'));
            }
        }

        $this->db->select('id')->from('athletes');
        if (strlen($query_array['fname']) || strlen($query_array['lname'])) {
            if (strlen($query_array['fname']) == 0) {
                $this->db->like('last_name', $query_array['lname']);
            } else if (strlen($query_array['lname']) == 0) {
                $this->db->like('first_name', $query_array['fname']);
            } else {
                $this->db->like('first_name', $query_array['fname']);
                $this->db->like('last_name', $query_array['lname']);
            }
        }
        $query = $this->db->get();
        $data['count'] = $query->num_rows();

        return $data;
    }

    /**
     * Checks if athlete with given id exists in database.
     * @param type $id
     * @return boolean
     */
    public function exists($id) {
        $this->db->where('id', $id);
        $this->db->from('athletes');
        return ($this->db->count_all_results() == 0 ? false : true);
    }

    /**
     * Fetches athlete from database.
     * @param type $id athlete id
     * @param type $withTeam
     * @return Athlete
     */
    public function fetch($id, $withTeam = false) {
        $this->db->select("`id`, `first_name`, `last_name`, `dob`, YEAR(dob) as dob_year , `gender`, `team_id`", false);
        $this->db->from('athletes');
        $this->db->where('id', $id);
        $athlete = $this->db->get()->row(0, 'Athlete');
        if ($withTeam) {
            $query = $this->db->get_where('teams', array('id' => $athlete->getTeamID()));
            $athlete->setTeam($query->row(0, 'Team'));
        }
        return $athlete;
    }

    /**
     * Fetches data to display table of athletes top five results based on FINA points.
     * @param type $id
     * @return result array
     */
    public function fetchTopFiveResults($id) {
        $this->db->select('`result`, `distance`, `stroke`, `date`, `pool`, `fina``');
        $this->db->from('`results`');
        $this->db->join('competitions', 'results.competition_id = competitions.id');
        $this->db->where('results.athlete_id', $id);
        $this->db->order_by('`fina`', 'desc');
        $this->db->limit(5);
        $results = $this->db->get()->result();
        return $results;
    }

    /**
     * Fetches data to display table of atheltes all time top results per event and pool.
     * @param type $id
     * @return result array
     */
    public function fetchTopResults($id) {
        if (!is_numeric($id))
            return array();
        $query = $this->db->query("SELECT competition_id, pool, distance, stroke, result, fina, results.date, c.city, c.name, c.country FROM results, competitions as c,(SELECT min(result_ms) as ms FROM results, competitions WHERE competitions.id = competition_id AND athlete_id=$id AND distance >= 50 AND NOT result_ms = " . MAX_INT . " GROUP BY stroke, distance, pool) sub WHERE sub.ms = result_ms AND athlete_id = $id AND competition_id = c.id ORDER BY CASE WHEN `stroke`='free' THEN 0 WHEN `stroke`='back' THEN 1 WHEN `stroke`='breast' THEN 2 WHEN `stroke`='fly' THEN 3 ELSE 4 END, distance, pool;");
//        $query = $this->db->query("SELECT `distance`,`stroke`, CASE WHEN `stroke`='free' THEN 0 WHEN `stroke`='back' THEN 1 WHEN `stroke`='breast' THEN 2 WHEN `stroke`='fly' THEN 3 ELSE 4 END as `order`, `pool`, `result`, `fina`, `date`, `city`, `country`, `name` FROM `results`, `competitions` WHERE `athlete_id`=$id AND results.competition_id=competitions.id AND `distance` >= 50 AND `results`.`result_ms` IN (SELECT min(`r`.`result_ms`) FROM `results` AS `r`, `competitions` AS `c` WHERE results.stroke = r.stroke AND results.distance = r.distance AND results.athlete_id = r.athlete_id AND c.id = r.competition_id GROUP BY `stroke`,`distance`,`pool` ORDER BY `r`.`result_ms`) GROUP BY `distance`,`stroke`,`pool` ORDER BY `order`, `distance`,`pool`;");
        return $query->result();
    }

    /**
     * Inserts athletes to database. 
     * @param array $athletes
     */
    public function insertNewAthletes($athletes) {
        foreach ($athletes as &$athlete) {
            $athlete['team_id'] = $this->TeamModel->team_id_from_full_name($athlete['team']);
            unset($athlete['ath_no']);
            unset($athlete['team']);
        }
        $this->db->ignore();
        $this->db->insert_batch('athletes', $athletes);
    }

    /**
     * Returns athlete id from names and dob.
     * @param type $first_name
     * @param type $last_name
     * @param type $dob
     * @return int
     */
    public function athlete_id_from_names_dob($first_name, $last_name, $dob) {
        $this->db->select('id');
        $this->db->from('athletes');
        $this->db->where('first_name', trim($first_name));
        $this->db->where('last_name', trim($last_name));
        $this->db->where('dob', $dob);
        $query = $this->db->get();
        if ($query->num_rows() == 0)
            return 0;
        return $query->row(0)->id;
    }

    /**
     * Returns athlete id from name and age.
     * @param type $first_name
     * @param type $last_name
     * @param type $dob
     * @return int
     */
    function athlete_id_from_name_age($first_name, $last_name, $dob) {
        $result = mysql_query("SELECT COUNT(`id`) FROM `athletes` WHERE `last_name`='$last_name' and `first_name`='$first_name' ") or die(mysql_error());
        $count = mysql_result($result, 0);
        if ($count == 0) {
            return 0;
        } else if ($count == 1) {
            $result = mysql_query("SELECT `id` FROM `athletes` WHERE `last_name`='$last_name' and `first_name`='$first_name' ") or die(mysql_error());
            return mysql_result($result, 0);
        } else {
            $result = mysql_query("SELECT `id` FROM `athletes` WHERE `last_name`='$last_name' and `first_name`='$first_name' ") or die(mysql_error());
            return mysql_result($result, 0);
// if two people with same name, select with age or some other value?
        }
    }

    //-------------------------Deprecated functions---------------------------//

    /**
     * Checks if athlete with exists.
     * @deprecated since version 1
     * @param type $first_name to compare
     * @param type $last_name to compare
     * @return boolean
     */
    function athlete_name_exists($first_name, $last_name) {

        $result = mysql_query("SELECT COUNT(`id`) FROM `athletes` WHERE `last_name`='$last_name' and `first_name`='$first_name' ") or die(mysql_error());
        return mysql_result($result, 0) >= 1;
    }

    /**
     * Checks if athlete exists.
     * @deprecated since version 1
     * @param type $first_name to compare
     * @param type $last_name to compare 
     * @param type $gender to compare
     * @param type $dob to compare
     * @param type $team_id to compare
     * @return boolean
     */
    function athlete_exists($first_name, $last_name, $gender, $dob, $team_id) {
        $result = mysql_query("SELECT COUNT(`id`) FROM `athletes` WHERE `last_name`='$last_name' and `first_name`='$first_name' and 
            `gender` = '$gender' and `dob` = '$dob' and `team_id` = $team_id") or die(mysql_error());
        return mysql_result($result, 0) >= 1;
    }

    /**
     * Checks if athlete exists.
     * @deprecated since version 1
     * @param type $first_name to compare
     * @param type $last_name to compare 
     * @param type $gender to compare
     * @param type $dob to compare
     * @param type $team_id to compare
     * @return boolean
     */
    public function exists_athlete($first_name, $last_name, $gender, $dob, $team_id) {
        $this->db->select('id');
        $this->db->from('athletes');
        $this->db->where('first_name', $first_name);
        $this->db->where('last_name', $last_name);
        $this->db->where('gender', $gender);
        $this->db->where('dob', $dob);
        $this->db->where('team_id', $team_id);
        $query = $this->db->get();
        if ($query->num_rows() == 0)
            return false;
        return $query->row(0)->id;
    }

    /**
     * Selects athletes from MDB database.
     * @deprecated since version 1
     * @param type $mdb database connection
     * @return array result array
     */
    public function athletes_from_mdb($mdb) {
        $athletes = $mdb->select('a.Last_name as last_name, a.First_name as first_name, Ath_sex as gender, Birth_date as dob, Team_name as team')->from('Athlete as a')->get()->result('Athlete');
        Utils::utf8_encode_deep($athletes);
        Utils::trim_deep($athletes);
        foreach ($athletes as /* @var $athlete Athlete */ $athlete) {
            $athlete->setTeamID($this->TeamModel->team_id_from_full_name($athlete->team));
            $athlete->setDob(date('Y-m-d', strtotime($athlete->getDob())));
        }
        return $athletes;
    }

    /**
     * Inserts athletes read from xml file to database.
     * @deprecated since version 1
     * @param array $athletes
     */
    function insert_athletes_to_db($athletes) {
        $query = "INSERT INTO `athletes`(`first_name`, `last_name`, `dob`, `gender`, `team_id`) VALUES ";
        $fields = '';
        foreach ($athletes as /* @var $athlete Athlete */ $athlete) {
            $fields .= '(\'' . implode('\', \'', $athlete->toArray()) . '\'), ';
        }
        $fields = substr($fields, 0, strlen($fields) - 2);

        $query .= $fields;
//    echo $query;
        mysql_query($query) or die(mysql_error());
    }

    /**
     * Used to print table of conflicted athletes based on name.
     * @deprecated since version 1
     */
    function print_conflicted_athletes() {
        $athletes = array();
        $result = mysql_query("SELECT `id`,`first_name`,`last_name`,`dob`,`gender`,`team_id` FROM `athletes`");
        while ($athlete = mysql_fetch_assoc($result)) {
            $athletes[] = $athlete;
        }
        $n = count($athletes);
        $found_conflicts = array();
        for ($i = 0; $i < $n; $i++) {
            $lhs = $athletes[$i]['last_name'] . $athletes[$i]['first_name'];
            $max = $i + 10 >= $n ? $n : $i + 10;
            $min = $i - 10 < 0 ? 0 : $i - 10;
//set min to 0 and max to n in order to search for all conflicted athlethes.
            for ($j = $min; $j < $max; $j++) {
                $rhs = $athletes[$j]['last_name'] . $athletes[$j]['first_name'];
                similar_text($lhs, $rhs, $p);
                if ($i != $j && $p > 90 && !in_array($rhs, $found_conflicts)) {
                    $found_conflicts[] = $lhs;
                    echo '<br><br>' . $lhs . ' - ' . $rhs . ' ' . $p . '%';
                }
            }
        }
    }

    /**
     * Prints table of found athletes from xml file.
     * @deprecated since version 1
     * @param array $results
     * @param type $file_name
     */
    function print_found_athletes(array $results, $file_name) {
        ?>
        <h2>Kokku leiti <?php echo count($results) ?> sportlast. </h2>
        <form action="getfile.php" method="post" >
            <input type="hidden" value="<?php echo $file_name ?>" name="file_name">
            <input type="submit" value="Lisa kõik andmebaasi" name="add_athletes">
        </form>

        <table class="klubid">
            <tr>
                <th>Eesnimi</th>
                <th>Perenimi</th>
                <th>Sugu</th>
                <th>Sünniaeg</th>
                <th>Klubi</th>
            </tr>

            <?php
            foreach ($results as /* @var $athlete Athlete */ $athlete) {

                $exists = ($athlete->getTeamID() !== 0);
                if (!$exists) {
                    echo '<tr class="team_missing">';
                } else {
                    echo '<tr>';
                }
                echo '<td>' . $athlete->getFirstName() . '</td>';
                echo '<td>' . $athlete->getLastName() . '</td>';
                echo '<td>' . $athlete->getGender() . '</td>';
                echo '<td>' . $athlete->getDob() . '</td>';
                if ($exists) {
                    echo '<td>' . team_full_name_from_id($athlete->getTeamID()) . '</td>';
                } else {
                    echo '<td>Ei leitud</td>';
                }
                echo '</tr>';
            }
            ?>
        </table>
        <?php
    }

    /**
     * Parses xml file to athlete.
     * @deprecated since version 1
     * @param type $data
     * @return \Athlete|null
     */
    function find_and_add_athlete_data($data) {
        if (!empty($data)) {
            $i = 0;
            $athlete = new Athlete();
            do {
                $object = $data->FormattedArea[0]->FormattedSections[0]->FormattedSection[0]
                        ->FormattedReportObjects[0]->FormattedReportObject[$i];
                if (empty($object)) {
//                if (!athlete_exists($athlete['first_name'], $athlete['last_name'], $athlete['gender'], $athlete['dob'], $athlete['team'])) {
//                    return $athlete;
//                }
                    if (!$this->athlete_exists($athlete->getFirstName(), $athlete->getLastName(), $athlete->getGender(), $athlete->getDob(), $athlete->getTeamID())) {
                        return $athlete;
                    }
                    return null;
                };
                $result = $object->ObjectName;
                switch ($result) {
                    case 'team1':
                        $athlete->setTeamID($this->TeamModel->team_id_from_full_name(strval($object->Value)));
                        $athlete->setTeam($this->TeamModel->team_from_id($athlete->getTeamID()));
//                    $athlete['team'] = team_id_from_full_name(strval($object->Value));
                        break;
                    case 'name1':
                        $names = explode(', ', strval($object->Value));
                        $athlete->setFirstName($names[1]);
                        $athlete->setLastName($names[0]);
//                    $athlete['first_name'] = $names[1];
//                    $athlete['last_name'] = $names[0];
                        break;
                    case 'birth1':
                        $athlete->setDob(strftime('%Y-%m-%d', strtotime($object->Value)));
//                    $athlete['dob'] = strftime('%Y-%m-%d', strtotime($object->Value));
                        break;
                    case 'indivsex1':
                        $athlete->setGender(strval($object->Value));
//                    $athlete['gender'] = strval($object->Value);
                        break;
                    default:
                        break;
                }
                $i++;
            } while ($i < 20);
        }
    }

    /**
     * Searches xml file for athletes.
     * @deprecated since version 1
     * @param type $path_to_xml
     * @return type
     */
    function athletes_from_xml_path($path_to_xml) {
        $athletes = array();
        $results_xml = simplexml_load_file($path_to_xml);
        $j = 0;
        do {
            $data = $results_xml->FormattedAreaPair[0]->FormattedAreaPair[$j];
            $j++;
            $athlete = $this->find_and_add_athlete_data($data);
            if ($athlete != NULL) {
                $athletes[] = $athlete;
            }
// max 5000 inserts at a time.
        } while (!empty($data) && $j < 5000);
        return $athletes;
    }

}
?>
